package db;

import interfaces.IFLocation;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.Customer;

public class LocationDb implements IFLocation {

	protected Connection con;

	public LocationDb() {
		connectDb();
	}

	public void connectDb() {
		con = DbConnection.getInstance().getDBcon();
	}

	public void disconnectDb() {
		DbConnection.getInstance();
		DbConnection.closeConnection();
	}
	@Override
	public String getCity(int zipcode) throws Exception {
		String query = 
				"SELECT * FROM Location " +
				"WHERE zipcode='" + zipcode + "'";
		ResultSet rs = null;
		String data = "";
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Searching zipcode ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			// rc = stmt.executeUpdate(query);
			rs = stmt.executeQuery(query);
			rs.next();
			data = rs.getString("city");
			stmt.close();
			System.out.println("SUCCESS: Querry executed!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
		return data;
	}

	@Override
	public String getCountry(int zipcode) throws Exception {
		String query = 
				"SELECT * FROM Location " +
				"WHERE zipcode='" + zipcode + "'";
		ResultSet rs = null;
		String data = "";
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Searching zipcode ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			// rc = stmt.executeUpdate(query);
			rs = stmt.executeQuery(query);
			rs.next();
			data = rs.getString("country");
			stmt.close();
			System.out.println("SUCCESS: Querry executed!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
		return data;
	}

	@Override
	public void insertLocation(int zipcode, String city, String country)
			throws Exception {
		String query = 
				"INSERT INTO Location (zipcode, city, country)" +
				"VALUES ('" + 
						zipcode + "','" +
						city + "','" +
						country + "')";
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Inserting Location ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeUpdate(query);
			//rs = stmt.executeQuery(query);
			stmt.close();
			System.out.println("SUCCESS: Querry executed!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
		
	}

}
